-- 元数据血缘关系表
CREATE TABLE DATA_METADATA_LINEAGE (
    -- 主键ID
    ID VARCHAR(64) NOT NULL,
    
    -- 采集任务ID
    COLLECTION_ID VARCHAR(64),
    
    -- 执行ID
    EXECUTION_ID VARCHAR(64),
    
    -- 版本号
    VERSION INTEGER,
    
    -- 表名
    TABLE_NAME VARCHAR(128),
    
    -- 外键名称
    FK_NAME VARCHAR(128),
    
    -- 外键列名
    FK_COLUMN_NAME VARCHAR(128),
    
    -- 主键表名
    PK_TABLE_NAME VARCHAR(128),
    
    -- 主键列名
    PK_COLUMN_NAME VARCHAR(128),
    
    -- 源元数据ID
    SOURCE_METADATA_ID VARCHAR(64),
    
    -- 目标元数据ID
    TARGET_METADATA_ID VARCHAR(64),
    
    -- 关系类型
    RELATION_TYPE VARCHAR(32),
    
    -- 关系描述
    DESCRIPTION VARCHAR(512),
    
    -- 关系权重
    WEIGHT INTEGER DEFAULT 1,
    
    -- 创建时间
    CREATE_TIME TIMESTAMP DEFAULT SYSDATE,
    
    -- 更新时间
    UPDATE_TIME TIMESTAMP DEFAULT SYSDATE,
    
    -- 是否删除（0-未删除，1-已删除）
    IS_DELETED INTEGER DEFAULT 0,
    
    -- 是否最新版本（0-否，1-是）
    IS_LATEST INTEGER DEFAULT 1,
    
    -- 创建人ID
    CREATOR_ID VARCHAR(64),
    
    -- 创建人姓名
    CREATOR_NAME VARCHAR(64),
    
    -- 更新人ID
    UPDATER_ID VARCHAR(64),
    
    -- 更新人姓名
    UPDATER_NAME VARCHAR(64),
    
    -- 设置主键约束
    CONSTRAINT PK_DATA_METADATA_LINEAGE PRIMARY KEY (ID)
);

-- 创建索引
CREATE INDEX IDX_METADATA_LINEAGE_COLLECTION_ID ON DATA_METADATA_LINEAGE (COLLECTION_ID);
CREATE INDEX IDX_METADATA_LINEAGE_SOURCE_ID ON DATA_METADATA_LINEAGE (SOURCE_METADATA_ID);
CREATE INDEX IDX_METADATA_LINEAGE_TARGET_ID ON DATA_METADATA_LINEAGE (TARGET_METADATA_ID);
CREATE INDEX IDX_METADATA_LINEAGE_TABLE_NAME ON DATA_METADATA_LINEAGE (TABLE_NAME);
CREATE INDEX IDX_METADATA_LINEAGE_VERSION ON DATA_METADATA_LINEAGE (VERSION);
CREATE INDEX IDX_METADATA_LINEAGE_IS_LATEST ON DATA_METADATA_LINEAGE (IS_LATEST);
CREATE INDEX IDX_METADATA_LINEAGE_IS_DELETED ON DATA_METADATA_LINEAGE (IS_DELETED);

-- 添加表约束
ALTER TABLE DATA_METADATA_LINEAGE ADD CONSTRAINT CK_METADATA_LINEAGE_IS_DELETED CHECK (IS_DELETED IN (0, 1));
ALTER TABLE DATA_METADATA_LINEAGE ADD CONSTRAINT CK_METADATA_LINEAGE_IS_LATEST CHECK (IS_LATEST IN (0, 1));
ALTER TABLE DATA_METADATA_LINEAGE ADD CONSTRAINT CK_METADATA_LINEAGE_WEIGHT CHECK (WEIGHT > 0);

-- 创建序列（用于生成自增ID）
CREATE SEQUENCE SEQ_METADATA_LINEAGE_ID
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

-- 创建触发器（自动生成ID）
CREATE OR REPLACE TRIGGER TRG_METADATA_LINEAGE_ID
BEFORE INSERT ON DATA_METADATA_LINEAGE
FOR EACH ROW
BEGIN
    IF :NEW.ID IS NULL THEN
        SELECT 'ML' || TO_CHAR(SYSDATE, 'YYYYMMDD') || LPAD(SEQ_METADATA_LINEAGE_ID.NEXTVAL, 6, '0')
        INTO :NEW.ID
        FROM DUAL;
    END IF;
END;
/

-- 创建触发器（自动更新更新时间）
CREATE OR REPLACE TRIGGER TRG_METADATA_LINEAGE_UPDATE
BEFORE UPDATE ON DATA_METADATA_LINEAGE
FOR EACH ROW
BEGIN
    :NEW.UPDATE_TIME := SYSDATE;
END;
/

-- 添加注释
COMMENT ON TABLE DATA_METADATA_LINEAGE IS '元数据血缘关系表';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.ID IS '主键ID';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.COLLECTION_ID IS '采集任务ID';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.EXECUTION_ID IS '执行ID';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.VERSION IS '版本号';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.TABLE_NAME IS '表名';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.FK_NAME IS '外键名称';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.FK_COLUMN_NAME IS '外键列名';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.PK_TABLE_NAME IS '主键表名';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.PK_COLUMN_NAME IS '主键列名';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.SOURCE_METADATA_ID IS '源元数据ID';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.TARGET_METADATA_ID IS '目标元数据ID';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.RELATION_TYPE IS '关系类型';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.DESCRIPTION IS '关系描述';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.WEIGHT IS '关系权重';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.IS_DELETED IS '是否删除（0-未删除，1-已删除）';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.IS_LATEST IS '是否最新版本（0-否，1-是）';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.CREATOR_ID IS '创建人ID';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.CREATOR_NAME IS '创建人姓名';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.UPDATER_ID IS '更新人ID';
COMMENT ON COLUMN DATA_METADATA_LINEAGE.UPDATER_NAME IS '更新人姓名';

-- 创建视图（查询最新版本的血缘关系）
CREATE OR REPLACE VIEW V_METADATA_LINEAGE_LATEST AS
SELECT *
FROM DATA_METADATA_LINEAGE
WHERE IS_LATEST = 1 AND IS_DELETED = 0;

-- 创建存储过程（更新血缘关系版本）
CREATE OR REPLACE PROCEDURE PROC_UPDATE_LINEAGE_VERSION(
    p_collection_id IN VARCHAR2,
    p_execution_id IN VARCHAR2
) AS
BEGIN
    -- 将所有旧版本标记为非最新
    UPDATE DATA_METADATA_LINEAGE
    SET IS_LATEST = 0
    WHERE COLLECTION_ID = p_collection_id
    AND EXECUTION_ID <> p_execution_id
    AND IS_LATEST = 1;
    
    COMMIT;
END;
/

-- 创建函数（获取最大版本号）
CREATE OR REPLACE FUNCTION FUNC_GET_MAX_VERSION(
    p_collection_id IN VARCHAR2
) RETURN NUMBER AS
    v_max_version NUMBER;
BEGIN
    SELECT COALESCE(MAX(VERSION), 0)
    INTO v_max_version
    FROM DATA_METADATA_LINEAGE
    WHERE COLLECTION_ID = p_collection_id
    AND IS_DELETED = 0;
    
    RETURN v_max_version;
END;
/

-- 插入示例数据
INSERT INTO DATA_METADATA_LINEAGE (
    ID, COLLECTION_ID, EXECUTION_ID, VERSION, TABLE_NAME,
    FK_NAME, FK_COLUMN_NAME, PK_TABLE_NAME, PK_COLUMN_NAME,
    SOURCE_METADATA_ID, TARGET_METADATA_ID, RELATION_TYPE,
    DESCRIPTION, WEIGHT, CREATE_TIME, UPDATE_TIME,
    IS_DELETED, IS_LATEST, CREATOR_ID, CREATOR_NAME
) VALUES (
    'ML20230101000001', 'COL20230101000001', 'EXE20230101000001', 1, 'CUSTOMER',
    'FK_ORDER_CUSTOMER', 'CUSTOMER_ID', 'CUSTOMER', 'ID',
    'MD20230101000001', 'MD20230101000002', 'FOREIGN_KEY',
    '客户与订单的关联关系', 1, SYSDATE, SYSDATE,
    0, 1, 'USER001', '管理员'
);

INSERT INTO DATA_METADATA_LINEAGE (
    ID, COLLECTION_ID, EXECUTION_ID, VERSION, TABLE_NAME,
    FK_NAME, FK_COLUMN_NAME, PK_TABLE_NAME, PK_COLUMN_NAME,
    SOURCE_METADATA_ID, TARGET_METADATA_ID, RELATION_TYPE,
    DESCRIPTION, WEIGHT, CREATE_TIME, UPDATE_TIME,
    IS_DELETED, IS_LATEST, CREATOR_ID, CREATOR_NAME
) VALUES (
    'ML20230101000002', 'COL20230101000001', 'EXE20230101000001', 1, 'ORDER',
    'FK_ORDER_ITEM_ORDER', 'ORDER_ID', 'ORDER', 'ID',
    'MD20230101000002', 'MD20230101000003', 'FOREIGN_KEY',
    '订单与订单项的关联关系', 1, SYSDATE, SYSDATE,
    0, 1, 'USER001', '管理员'
);

-- 创建查询血缘关系的存储过程
CREATE OR REPLACE PROCEDURE PROC_GET_LINEAGE(
    p_metadata_id IN VARCHAR2,
    p_direction IN VARCHAR2, -- 'UPSTREAM' or 'DOWNSTREAM'
    p_depth IN NUMBER DEFAULT 1
) AS
BEGIN
    IF p_direction = 'UPSTREAM' THEN
        -- 查询上游血缘关系
        WITH RECURSIVE LINEAGE_CTE(SOURCE_ID, TARGET_ID, LEVEL) AS (
            -- 基础查询
            SELECT SOURCE_METADATA_ID, TARGET_METADATA_ID, 1
            FROM DATA_METADATA_LINEAGE
            WHERE TARGET_METADATA_ID = p_metadata_id
            AND IS_DELETED = 0
            AND IS_LATEST = 1
            
            UNION ALL
            
            -- 递归查询
            SELECT L.SOURCE_METADATA_ID, L.TARGET_METADATA_ID, CTE.LEVEL + 1
            FROM DATA_METADATA_LINEAGE L
            JOIN LINEAGE_CTE CTE ON L.TARGET_METADATA_ID = CTE.SOURCE_ID
            WHERE L.IS_DELETED = 0
            AND L.IS_LATEST = 1
            AND CTE.LEVEL < p_depth
        )
        SELECT * FROM LINEAGE_CTE;
    ELSE
        -- 查询下游血缘关系
        WITH RECURSIVE LINEAGE_CTE(SOURCE_ID, TARGET_ID, LEVEL) AS (
            -- 基础查询
            SELECT SOURCE_METADATA_ID, TARGET_METADATA_ID, 1
            FROM DATA_METADATA_LINEAGE
            WHERE SOURCE_METADATA_ID = p_metadata_id
            AND IS_DELETED = 0
            AND IS_LATEST = 1
            
            UNION ALL
            
            -- 递归查询
            SELECT L.SOURCE_METADATA_ID, L.TARGET_METADATA_ID, CTE.LEVEL + 1
            FROM DATA_METADATA_LINEAGE L
            JOIN LINEAGE_CTE CTE ON L.SOURCE_METADATA_ID = CTE.TARGET_ID
            WHERE L.IS_DELETED = 0
            AND L.IS_LATEST = 1
            AND CTE.LEVEL < p_depth
        )
        SELECT * FROM LINEAGE_CTE;
    END IF;
END;
/

-- 创建完整血缘关系图的存储过程
CREATE OR REPLACE PROCEDURE PROC_GET_COMPLETE_LINEAGE_GRAPH(
    p_metadata_id IN VARCHAR2
) AS
BEGIN
    -- 查询上下游血缘关系
    WITH UPSTREAM AS (
        -- 上游血缘关系
        WITH RECURSIVE LINEAGE_CTE(SOURCE_ID, TARGET_ID, LEVEL) AS (
            -- 基础查询
            SELECT SOURCE_METADATA_ID, TARGET_METADATA_ID, 1
            FROM DATA_METADATA_LINEAGE
            WHERE TARGET_METADATA_ID = p_metadata_id
            AND IS_DELETED = 0
            AND IS_LATEST = 1
            
            UNION ALL
            
            -- 递归查询
            SELECT L.SOURCE_METADATA_ID, L.TARGET_METADATA_ID, CTE.LEVEL + 1
            FROM DATA_METADATA_LINEAGE L
            JOIN LINEAGE_CTE CTE ON L.TARGET_METADATA_ID = CTE.SOURCE_ID
            WHERE L.IS_DELETED = 0
            AND L.IS_LATEST = 1
            AND CTE.LEVEL < 3
        )
        SELECT SOURCE_ID, TARGET_ID, LEVEL, 'UPSTREAM' AS DIRECTION
        FROM LINEAGE_CTE
    ),
    DOWNSTREAM AS (
        -- 下游血缘关系
        WITH RECURSIVE LINEAGE_CTE(SOURCE_ID, TARGET_ID, LEVEL) AS (
            -- 基础查询
            SELECT SOURCE_METADATA_ID, TARGET_METADATA_ID, 1
            FROM DATA_METADATA_LINEAGE
            WHERE SOURCE_METADATA_ID = p_metadata_id
            AND IS_DELETED = 0
            AND IS_LATEST = 1
            
            UNION ALL
            
            -- 递归查询
            SELECT L.SOURCE_METADATA_ID, L.TARGET_METADATA_ID, CTE.LEVEL + 1
            FROM DATA_METADATA_LINEAGE L
            JOIN LINEAGE_CTE CTE ON L.SOURCE_METADATA_ID = CTE.TARGET_ID
            WHERE L.IS_DELETED = 0
            AND L.IS_LATEST = 1
            AND CTE.LEVEL < 3
        )
        SELECT SOURCE_ID, TARGET_ID, LEVEL, 'DOWNSTREAM' AS DIRECTION
        FROM LINEAGE_CTE
    )
    SELECT * FROM UPSTREAM
    UNION ALL
    SELECT * FROM DOWNSTREAM;
END;
/ 